﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Web.Script.Serialization;
using KSSModel;
using KSSModel.report;
using System.Data;
using Newtonsoft.Json;
using System.Globalization;

namespace KSSOnline.Controller
{
    public class ChartHelper
    {
        public static string[] NawaLocation = { "NIT - Rayong", "NPI - Rayong", "NPI - Bangsue", "NPI(SB) - Saraburi", "NPI(SB) - Bangsue" };

        public static string GetPieChart(int year)
        {
            using (var dataContext = new KSSDBDataContext())
            {
                PieChart pie = new PieChart();
                pie.data = new List<PieChartDetail>();
                pie.name = year.ToString();
                foreach (var item in NawaLocation)
                {
                    var query = from ds in dataContext.tblDocs
                                join emp in dataContext.tblEDMSHRs on ds.departmentName.Trim() equals emp.sacnameTh.Trim()
                                where ds.createdDate.Year == year && emp.mlocation == item
                                select ds;
                    PieChartDetail pd = new PieChartDetail();
                    pd.name = item;
                    pd.y = query.Count();
                    pie.data.Add(pd);
                }

                var aView = JsonConvert.SerializeObject(pie);
                return aView;
            }
        }


        public static string GetDocGrpByMonthByLocation(int year, string location)
        {
            using (var dataContext = new KSSDBDataContext())
            {

                try
                {
                    ReportA1 r = new ReportA1();
                    r.yAis = new List<YAis>();
                    YAis yais = new YAis();
                    YAis yaisexpect = new YAis();
                    YAis yaissum = new YAis();
                    yais.name = location;
                    yaisexpect.name = "Target";
                    yaissum.name = "Accumulate";
                    List<Int32> data = new List<int>();
                    List<Int32> dataexpect = new List<int>();
                    List<Int32> datasum = new List<int>();
                    var query = from ds in dataContext.tblDocs
                                join emp in dataContext.tblEDMSHRs on ds.departmentName.Trim() equals emp.sacnameTh.Trim()
                                where ds.createdDate.Year == year && emp.mlocation == location
                                group ds by ds.createdDate.Month into g
                                select new
                                {
                                    month = g.Key,
                                    docCount = g.Count()
                                };
                    var queryexpect = from ed in dataContext.tblExpectDocs
                                      select new
                                      {
                                          ed.id,
                                          ed.expectNumber,
                                          ed.department,
                                          month = ed.datetime.Month,
                                          year = ed.datetime.Year,
                                          ed.isEnable

                                      };
                    var sum = 0;
                    for (int i = 1; i < 13; i++)
                    {
                        var n = query.ToList().Where(k => k.month == i);
                        if (n != null && n.Count() > 0)
                        {
                            data.Add(n.First().docCount);
                            sum += n.First().docCount;
                        }
                        else
                        {
                            data.Add(0);
                        }
                        datasum.Add(sum);
                        var ne = queryexpect.ToList().Where(k => k.month == i);
                        if (ne != null && ne.Count() > 0)
                        {
                            dataexpect.Add(ne.First().expectNumber);
                        }
                        else
                        {
                            dataexpect.Add(0);
                        }
                    }
                    yais.data = data;
                    yaisexpect.data = dataexpect;
                    yaissum.data = datasum;
                    r.yAis.Add(yais);
                    r.yAis.Add(yaisexpect);
                    r.yAis.Add(yaissum);
                    var aView = JsonConvert.SerializeObject(r);
                    return aView;
                }
                catch (Exception ex)
                {
                    KSSMaster.AddLog(new tblLog()
                    {
                        details =
                            ex.Message,
                        logType = 1
                    });
                    return JsonConvert.SerializeObject(new Result(false, ex.Message));
                }
            }
        }

        public static string GetDocGrpByMonth(int year)
        {
            using (var dataContext = new KSSDBDataContext())
            {
                var serializer = new JavaScriptSerializer();
                try
                {
                    var query = from ds in dataContext.tblDocs
                                where ds.createdDate.Year == year
                                group ds by ds.createdDate.Month into g
                                select new
                                {
                                    month = g.Key,
                                    docCount = g.Count()
                                };
                    var aView = serializer.Serialize(query);
                    return aView;
                }
                catch (Exception ex)
                {
                    KSSMaster.AddLog(new tblLog()
                    {
                        details =
                            ex.Message,
                        logType = 1
                    });
                    return serializer.Serialize(new Result(false, ex.Message));
                }
            }
        }

        public static string GetBarTargetYear()
        {
            using (var dataContext = new KSSDBDataContext())
            {
                var q = from d in dataContext.kssMonthlyTargets
                        group d by d.Year into g
                        select new
                        {
                            name = g.Key,
                        };
                return JsonConvert.SerializeObject(q);
            }
        }

        public static string GetBarTargetBySectionByYear(int year)
        {
            using (var dataContext = new KSSDBDataContext())
            {
                var q = from d in dataContext.kssMonthlyTargets
                        where d.Year == year
                        group d by d.SectionName into g
                        select new
                        {
                            name = g.Key,
                            number = g.Sum(_ => _.Target)
                        };

                ReportA1 r = new ReportA1();
                r.xAis = new List<string>();
                r.yAis = new List<YAis>();
                List<Int32> datas = new List<int>();
                List<Int32> acutalDatas = new List<int>();


                foreach (var item in q)
                {
                    var c = (from dd in dataContext.tblDocs
                             join hr in dataContext.tblEDMSHRs on dd.createdBy equals hr.mstaffno
                             where dd.createdDate.Year == year && hr.sacnameTh == item.name
                             select dd).Count();
                    acutalDatas.Add(c);
                    r.xAis.Add(item.name);
                    datas.Add(item.number);

                }
                r.yAis.Add(new YAis() { name = "Plan", data = datas });
                r.yAis.Add(new YAis() { name = "Actual", data = acutalDatas });
                return JsonConvert.SerializeObject(r);
            }
        }

        public static string GetBarChartEveryYear()
        {
            using (var dataContext = new KSSDBDataContext())
            {
                var q = from d in dataContext.tblDocs
                        group d by d.createdDate.Year into g
                        select new
                        {
                            year = g.Key,
                            count = g.Count()
                        };
                ReportA1 r = new ReportA1();
                r.xAis = new List<string>();
                r.yAis = new List<YAis>();
                List<Int32> datas = new List<int>();
                foreach (var item in q)
                {

                    r.xAis.Add(item.year.ToString());
                    datas.Add(item.count);

                }
                r.xAis.Add("Total");

                datas.Add(datas.Sum());
                r.yAis.Add(new YAis() { name = "จำนวนเรื่อง MP Sheet", data = datas });
                return JsonConvert.SerializeObject(r);
            }
        }


        public static string GetBarChartByYear()
        {
            using (var dataContext = new KSSDBDataContext())
            {
                int year = DateTime.Today.Year;
                ReportA1 r = new ReportA1();

                var getCate = from cate in dataContext.tblA1Cates
                              where cate.section == 3
                              select cate;
                r.xAis = getCate.Select(k => k.a1CateName).ToList();
                r.xAis.Add("Total");
                r.yAis = new List<YAis>();
                YAis yais = new YAis();
                yais.name = year.ToString();
                List<Int32> datas = new List<int>();
                foreach (var item in getCate)
                {
                    var getSubItem12 = (from at in dataContext.tblA1Items
                                        where at.a1CateId == item.a1CateId
                                        select at.a1ItemId.ToString()).ToArray();
                    var resultcate = (from d in dataContext.tblDocs
                                      join dp in dataContext.docPropertyDatas
                                          on d.id equals dp.docId
                                      where d.createdDate.Year == year && getSubItem12.Contains(dp.propertyName) && dp.dataNvarchar.ToString().ToLower() == "true"
                                      select new tblDocSearch
                                      {
                                          id = d.id,
                                          createdByName = d.createdByName,
                                          createdBy = d.createdBy,
                                          departmentName = d.departmentName,
                                          title = d.title,
                                          cash = d.cash,
                                          createdDate = d.createdDate,
                                          status = d.status,
                                          score = d.score,
                                          CostSaving = ""
                                      }).Distinct().ToList();
                    datas.Add(resultcate.Count);
                }
                datas.Add(datas.Sum());
                yais.data = datas;
                r.yAis.Add(yais);
                return JsonConvert.SerializeObject(r);
            }


        }

        public static string GetBarChart2(Int32 section)
        {
            using (var dataContext = new KSSDBDataContext())
            {
                var serializer = new JavaScriptSerializer();
                try
                {
                    var A1Cate = (from cate in dataContext.tblA1Cates
                                  join cateLocation in dataContext.tblA1CateLocations
                                  on cate.a1CateId equals cateLocation.a1CateId
                                  where cate.section == section
                                  select
                                      new
                                      {
                                          cate.a1CateId,
                                          cate.a1CateName,
                                          cateLocation.locationId,
                                          cate.isDisable,
                                      }).ToList();

                    var aView = serializer.Serialize(A1Cate);
                    ReportA1 reportData = new ReportA1();
                    reportData.xAis = A1Cate.Select(k => k.a1CateName).ToList();
                    reportData.yAis = new List<YAis>();


                    foreach (var item in A1Cate)
                    {
                        List<int> data = new List<int>();
                        var subquery = from sub in dataContext.tblA1Items
                                       where sub.a1CateId == item.a1CateId
                                       select sub;

                        foreach (var item2 in subquery)
                        {

                            var query = (from dsp in dataContext.docPropertyDatas
                                         where (dsp.propertyName == item2.a1ItemId.ToString()) && dsp.dataNvarchar.ToString().ToLower() == "true"
                                         select new
                                         {
                                             docid = dsp.docId,
                                             propertyname = dsp.propertyName
                                         }).Distinct().ToList();

                            YAis y = new YAis();
                            y.name = item2.a1ItemName;
                            if (reportData.yAis.Where(k => k.name == item2.a1ItemName).Count() == 0)
                            {
                                reportData.yAis.Add(y);
                            }

                        }


                    }

                    foreach (var item in reportData.yAis)
                    {
                        List<int> data = new List<int>();
                        foreach (var item2 in reportData.xAis)
                        {

                            var query = (from dsp in dataContext.docPropertyDatas
                                         join a1 in dataContext.tblA1Items on dsp.propertyName equals a1.a1ItemId.ToString()
                                         join c1 in dataContext.tblA1Cates on a1.a1CateId equals c1.a1CateId
                                         //where (c1.a1CateName == item2) && dsp.dataNvarchar.ToString().ToLower() == "true"
                                         where (c1.a1CateName == item2) && a1.a1ItemName == item.name.ToString() && dsp.dataNvarchar.ToString().ToLower() == "true"
                                         select new
                                         {
                                             docid = dsp.docId,
                                             propertyname = dsp.propertyName
                                         });
                            data.Add(query.Count());
                        }
                        item.data = data;


                    }



                    return JsonConvert.SerializeObject(reportData); ;


                }
                catch (Exception ex)
                {
                    return serializer.Serialize(new Result(false, ex.Message));
                }

            }
        }

        public static string GetG1(int cateid, int cateColumnId)
        {
            using (var dataContext = new KSSDBDataContext())
            {
                List<KSSModel.report.ReportA1> A1s = new List<KSSModel.report.ReportA1>();
                var querySubCateImprove = from a1Item in dataContext.tblA1Items
                                          join cateItem in dataContext.tblA1Cates
                                          on a1Item.a1CateId equals cateItem.a1CateId
                                          where a1Item.a1CateId == cateid
                                          select a1Item;

                var queryGetColumn = from a1Item in dataContext.tblA1Items
                                     join cateItem in dataContext.tblA1Cates
                                          on a1Item.a1CateId equals cateItem.a1CateId
                                     where a1Item.a1CateId == cateColumnId
                                     select a1Item;

                ReportA1 reportData = new ReportA1();
                reportData.xAis = querySubCateImprove.Select(k => k.a1ItemName).ToList();
                reportData.yAis = new List<YAis>();
                foreach (var r in queryGetColumn)
                {

                    YAis yXis = new YAis();
                    yXis.name = r.a1ItemName;
                    List<int> datas = new List<int>();
                    foreach (var c in querySubCateImprove)
                    {
                        var query = (from dsp in dataContext.docPropertyDatas
                                     where (dsp.propertyName == r.a1ItemId.ToString() || dsp.propertyName == c.a1ItemId.ToString()) && dsp.dataNvarchar.ToString().ToLower() == "true"
                                     select new
                                     {
                                         docid = dsp.docId,
                                         propertyname = dsp.propertyName
                                     }).Distinct().ToList();

                        var valueCell = from count in query
                                        group count by count.docid into g
                                        where g.Count() > 1
                                        select g.Key;

                        datas.Add(valueCell.Count());

                    }
                    yXis.data = datas;
                    reportData.yAis.Add(yXis);
                }




                //var aView = serializer.Serialize(dt);
                return JsonConvert.SerializeObject(reportData);
            }

        }

        public static string GetDocGrpA1(string typeId)
        {
            using (var dataContext = new KSSDBDataContext())
            {
                var serializer = new JavaScriptSerializer();
                try
                {
                    var query = from ds in dataContext.tblDocs
                                join dsp in dataContext.docPropertyDatas on ds.id equals dsp.docId
                                join a1Item in dataContext.tblA1Items on dsp.propertyName equals a1Item.a1ItemId.ToString()
                                join a1Cate in dataContext.tblA1Cates on a1Item.a1CateId equals a1Cate.a1CateId
                                where dsp.propertyName == typeId && dsp.dataNvarchar.ToString().ToLower() == "true"
                                group ds by ds.id into g
                                select new
                                {
                                    department = g.First().departmentName,
                                    cate = g.Count()
                                }
                                ;
                    var aView = serializer.Serialize(query);
                    return aView;
                }
                catch (Exception ex)
                {
                    KSSMaster.AddLog(new tblLog()
                    {
                        details =
                            ex.Message,
                        logType = 1
                    });
                    return serializer.Serialize(new Result(false, ex.Message));
                }
            }
        }
        public static string GetDocGrpA1()
        {
            using (var dataContext = new KSSDBDataContext())
            {
                var serializer = new JavaScriptSerializer();
                try
                {
                    var query = from ds in dataContext.tblDocs
                                join dsp in dataContext.docPropertyDatas on ds.id equals dsp.docId
                                join a1Item in dataContext.tblA1Items on dsp.propertyName equals
                                    a1Item.a1ItemId.ToString()
                                join a1Cate in dataContext.tblA1Cates on a1Item.a1CateId equals a1Cate.a1CateId
                                where dsp.dataNvarchar.ToString().ToLower() == "true"
                                group ds by ds.departmentName
                                    into g
                                    select new
                                    {
                                        department = g.First().departmentName,
                                        cate = g.Count()
                                    };
                    var groupbyType = query.GroupBy(k => k.cate);

                    var aView = serializer.Serialize(groupbyType);
                    return aView;
                }
                catch (Exception ex)
                {
                    KSSMaster.AddLog(new tblLog()
                    {
                        details =
                            ex.Message,
                        logType = 1
                    });
                    return serializer.Serialize(new Result(false, ex.Message));
                }
            }
        }

        public static string GetDocGrpByCateA1()
        {
            using (var dataContext = new KSSDBDataContext())
            {
                var serializer = new JavaScriptSerializer();
                try
                {
                    var query = from ds in dataContext.tblDocs
                                join dsp in dataContext.docPropertyDatas on ds.id equals dsp.docId
                                join a1Item in dataContext.tblA1Items on dsp.propertyName equals
                                    a1Item.a1ItemId.ToString()
                                join a1Cate in dataContext.tblA1Cates on a1Item.a1CateId equals a1Cate.a1CateId
                                where dsp.dataNvarchar.ToString().ToLower() == "true"
                                group a1Cate by a1Cate.a1CateId
                                    into g
                                    select new
                                    {
                                        department = g.First().a1CateName,
                                        cate = g.Count()

                                    };
                    //var groupbyType = query.GroupBy(k => k.cate);

                    var aView = serializer.Serialize(query);
                    return aView;
                }
                catch (Exception ex)
                {
                    KSSMaster.AddLog(new tblLog()
                    {
                        details =
                            ex.Message,
                        logType = 1
                    });
                    return serializer.Serialize(new Result(false, ex.Message));
                }
            }
        }

        public static string GetDocGrpByCateA1(string typeId)
        {
            using (var dataContext = new KSSDBDataContext())
            {
                var serializer = new JavaScriptSerializer();
                try
                {
                    var query = from ds in dataContext.tblDocs
                                join dsp in dataContext.docPropertyDatas on ds.id equals dsp.docId
                                join a1Item in dataContext.tblA1Items on dsp.propertyName equals
                                    a1Item.a1ItemId.ToString()
                                join a1Cate in dataContext.tblA1Cates on a1Item.a1CateId equals a1Cate.a1CateId
                                where dsp.propertyName == typeId && dsp.dataNvarchar.ToString().ToLower() == "true"
                                group ds by a1Cate.a1CateId
                                    into g
                                    select new
                                    {
                                        department = g.First().departmentName,
                                        cate = g.Count()

                                    };
                    var groupbyType = query.GroupBy(k => k.cate);

                    var aView = serializer.Serialize(groupbyType);
                    return aView;
                }
                catch (Exception ex)
                {
                    KSSMaster.AddLog(new tblLog()
                    {
                        details =
                            ex.Message,
                        logType = 1
                    });
                    return serializer.Serialize(new Result(false, ex.Message));
                }
            }
        }

        public static string GetDocGrpA1NoGroup()
        {
            using (var dataContext = new KSSDBDataContext())
            {
                var serializer = new JavaScriptSerializer();
                try
                {
                    var query = from ds in dataContext.tblDocs
                                join dsp in dataContext.docPropertyDatas on ds.id equals dsp.docId

                                select new
                                {
                                    departmentName = ds.departmentName,
                                    propertyName = dsp.propertyName,
                                    value = dsp.dataNvarchar
                                };
                    // var groupbyType = query.GroupBy(k => k.cate);

                    var aView = serializer.Serialize(query);
                    return aView;
                }
                catch (Exception ex)
                {
                    KSSMaster.AddLog(new tblLog()
                    {
                        details =
                            ex.Message,
                        logType = 1
                    });
                    return serializer.Serialize(new Result(false, ex.Message));
                }
            }
        }
        public static string GetDocGrpByCash()
        {
            using (var dataContext = new KSSDBDataContext())
            {
                var serializer = new JavaScriptSerializer();
                try
                {
                    var query = from ds in dataContext.tblDocs

                                group ds by new { ds.cash, ds.createdDate.Year }
                                    into g
                                    select new
                                    {
                                        cash = g.First().cash,
                                        number = g.Count(),
                                        year = g.First().createdDate.Year
                                    };

                    var aView = serializer.Serialize(query);
                    return aView;
                }
                catch (Exception ex)
                {
                    KSSMaster.AddLog(new tblLog()
                    {
                        details =
                            ex.Message,
                        logType = 1
                    });
                    return serializer.Serialize(new Result(false, ex.Message));
                }
            }
        }
        public static string AddExpectDoc(tblExpectDoc expectDoc)
        {
            using (var dataContext = new KSSDBDataContext())
            {
                var serializer = new JavaScriptSerializer();
                try
                {
                    if (dataContext.tblExpectDocs.Any(i => i.datetime.Year == expectDoc.datetime.Year && i.datetime.Month == expectDoc.datetime.Month))
                    {

                        var exits = dataContext.tblExpectDocs.Single(i => i.datetime.Year == expectDoc.datetime.Year && i.datetime.Month == expectDoc.datetime.Month);
                        if (exits != null)
                        {
                            if (!string.IsNullOrEmpty(expectDoc.department) && exits.department != expectDoc.department)
                            {
                                exits.department = expectDoc.department;
                            }
                            if (!string.IsNullOrEmpty(expectDoc.expectNumber.ToString()) && exits.expectNumber != expectDoc.expectNumber)
                            {
                                exits.expectNumber = expectDoc.expectNumber;
                            }
                            dataContext.SubmitChanges();
                            return serializer.Serialize(new Result(true));
                        }
                    }
                    else
                    {
                        dataContext.tblExpectDocs.InsertOnSubmit(expectDoc);
                        dataContext.SubmitChanges();
                        var log = new StringBuilder();
                        log.Append("เพิ่มเป้าของเอกสารของวันที่ " + expectDoc.datetime + "โดย " +
                                   System.Web.HttpContext.Current.User.Identity.Name);
                        KSSMaster.AddLog(new tblLog()
                        {
                            details =
                                                     log.ToString(),
                            username = "system",
                            logType = 0
                        });
                        return serializer.Serialize(new Result(true));
                    }
                    return serializer.Serialize(new Result(true));
                }

                catch (Exception ex)
                {
                    KSSMaster.AddLog(new tblLog()
                    {
                        details =
                            ex.Message,
                        logType = 1
                    });
                    return serializer.Serialize(new Result(false, ex.Message)); ;
                }

            }
        }
        public static string EditExpectDoc(List<tblExpectDoc> expectDocs)
        {
            using (var dataContext = new KSSDBDataContext())
            {
                var serializer = new JavaScriptSerializer();
                try
                {
                    foreach (var expectDoc in expectDocs)
                    {
                        if (dataContext.tblExpectDocs.Any(i => i.id == expectDoc.id))
                        {
                            var exits = dataContext.tblExpectDocs.Single(ed => ed.id == expectDoc.id);
                            if (exits != null)
                            {
                                if (!string.IsNullOrEmpty(expectDoc.department) && exits.department != expectDoc.department)
                                {
                                    exits.department = expectDoc.department;
                                }
                                if (!string.IsNullOrEmpty(expectDoc.expectNumber.ToString()) && exits.expectNumber != expectDoc.expectNumber)
                                {
                                    exits.expectNumber = expectDoc.expectNumber;
                                }
                                exits.datetime = expectDoc.datetime;
                            }
                        }
                        //dataContext.tblExpectDocs.InsertOnSubmit(expectDoc);
                    }
                    dataContext.SubmitChanges();
                    var log = new StringBuilder();
                    log.Append("แก้ไขเป้าของเอกสารโดย " + System.Web.HttpContext.Current.User.Identity.Name);
                    KSSMaster.AddLog(new tblLog()
                    {
                        details =
                            log.ToString(),
                        username = "system",
                        logType = 0
                    });
                    return serializer.Serialize(new Result(true));
                }
                catch (Exception ex)
                {
                    KSSMaster.AddLog(new tblLog()
                    {
                        details =
                            ex.Message,
                        logType = 1
                    });
                    return serializer.Serialize(new Result(false, ex.Message));
                }

            }
        }

        public static string DeleteExpectDoc(int expectDocID)
        {
            using (var dataContext = new KSSDBDataContext())
            {
                var serializer = new JavaScriptSerializer();
                try
                {
                    string deleteExpecDoc =
                   "DELETE FROM tblExpectDoc WHERE id =" + expectDocID;
                    dataContext.ExecuteCommand(deleteExpecDoc);
                }
                catch (Exception ex)
                {
                    return serializer.Serialize(new Result(false, ex.Message));
                }
                return serializer.Serialize(new Result(true));
            }
        }

        public static string GetExpectDoc()
        {
            using (var dataContext = new KSSDBDataContext())
            {
                var serializer = new JavaScriptSerializer();
                try
                {
                    var query = from ed in dataContext.tblExpectDocs
                                select new
                                {
                                    ed.id,
                                    ed.expectNumber,
                                    ed.department,
                                    month = ed.datetime.Month,
                                    year = ed.datetime.Year,
                                    ed.isEnable

                                };
                    var aView = serializer.Serialize(query);
                    return aView;
                }
                catch (Exception ex)
                {
                    KSSMaster.AddLog(new tblLog()
                    {
                        details =
                            ex.Message,
                        logType = 1
                    });
                    return serializer.Serialize(new Result(false, ex.Message));
                }
            }
        }
    }
}
